import 'package:sqlite3/sqlite3.dart';
import '../model/model_dictionary_word.dart';
import '../model/model_wordbook_word.dart';
import '../tools/tools_database.dart';

class DaoDictionaryWord {
  static Future<int> insert(ModelDictionaryWord l) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
    INSERT INTO 
      p001_word_dictionary(
        word,soundmark,
        explain,info,remark)
    VALUES
      (?,?,?,?,?)
    ;
    ''';
    final stmt = db.prepare(sql);
    stmt..execute([l.word, l.soundmark, l.explain, l.info, l.remark]);

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static Future<int> deleteById(int id) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      delete from 
        p001_word_dictionary
      where 
        id = ?;
    ''';
    final stmt = db.prepare(sql);
    stmt..execute([id]);

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static Future<int> updateById(ModelDictionaryWord l) async {
    print(l);
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      update 
        p001_word_dictionary
      set
        word=?,
        soundmark=?,
        explain= ?,
        info = ?,
        remark=?
      where
        id = ?
      ;
    ''';

    final stmt = db.prepare(sql);
    try {
      int gmt_modified = DateTime.now().millisecondsSinceEpoch;
      stmt..execute([l.word, l.soundmark, l.explain, l.info, l.remark, l.id]);
      print("执行成功");
    } catch (e) {
      print("出现异常");
      print(e);
    }

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static Future<List<ModelDictionaryWord>> findAll() async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_word_dictionary
      ;
    ''';
    ResultSet rs = db.select(sql);
    Iterator<Row> i = rs.iterator;
    List<ModelDictionaryWord> list = [];
    while (i.moveNext()) {
      Row r = i.current;
      print(r);

      ModelDictionaryWord w = new ModelDictionaryWord();
      w.id = r["id"];
      w.word = r["word"];
      w.soundmark = r["soundmark"];
      w.explain = r["explain"];
      w.info = r["info"];
      w.remark = r["remark"];

      list.add(w);
    }

    // 3. 关闭连接
    db.dispose();
    return list;
  }

  static Future<ModelDictionaryWord?> findById(int id) async {
    ModelDictionaryWord? m = null;

    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_word_dictionary
      where
        id = ?
      ;
    ''';
    ModelDictionaryWord? w = null;
    ResultSet rs = db.select(sql, [id]);
    Iterator<Row> i = rs.iterator;
    if (i.moveNext()) {
      Row r = i.current;
      print(r);

      w = new ModelDictionaryWord();
      w.id = r["id"];
      w.word = r["word"];
      w.soundmark = r["soundmark"];
      w.explain = r["explain"];
      w.info = r["info"];
      w.remark = r["remark"];
    }

    // 3. 关闭连接
    db.dispose();

    return m;
  }

  static Future<ModelDictionaryWord?> findByWord(String word) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_word_dictionary
      where
        word = ?
      ;
    ''';
    ModelDictionaryWord? w = null;
    ResultSet rs = db.select(sql, [word]);
    Iterator<Row> i = rs.iterator;
    if (i.moveNext()) {
      Row r = i.current;
      // print(r);

      w = new ModelDictionaryWord();
      w.id = r["id"];
      w.word = r["word"];
      w.soundmark = r["soundmark"];
      w.explain = r["explain"];
      w.info = r["info"];
      w.remark = r["remark"];
    }

    // 3. 关闭连接
    db.dispose();

    print(w);
    return w;
  }

  static Future<List<ModelDictionaryWord>> findAllLikeWord(String word) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_word_dictionary
      where
        word like '?'
      ;
    ''';
    ResultSet rs = db.select(sql, ["%" + word + "%"]);
    Iterator<Row> i = rs.iterator;
    List<ModelDictionaryWord> list = [];
    while (i.moveNext()) {
      Row r = i.current;
      print(r);

      ModelDictionaryWord w = new ModelDictionaryWord();
      w.id = r["id"];
      w.word = r["word"];
      w.soundmark = r["soundmark"];
      w.explain = r["explain"];
      w.info = r["info"];
      w.remark = r["remark"];

      list.add(w);
    }

    // 3. 关闭连接
    db.dispose();
    return list;
  }
}
